package com.telecom.cms.dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.telecom.cms.dto.CampCustAppdRsltTemp;
import com.telecom.cms.util.Constant;

public class CampCustAppdRsltTempDAO {

	Dao dao = new DaoImp();

	String db = (new Constant()).getPara("DATABASE");

	public int insert(CampCustAppdRsltTemp transientInstance) {
		int rs = 0;
		String sql = "select * from  " + db
				+ ".Camp_Cust_Appd_Rslt_Temp where seq_id="
				+ transientInstance.getSeqId();
		try {
			ResultSet rs2 = dao.select(sql);
			if (rs2.next())
				sql = "update " + db + ".Camp_Cust_Appd_Rslt_Temp set seq_id='"

				+ transientInstance.getSeqId() + "',latn_id='"
						+ transientInstance.getLatnId() + "',cust_id='"
						+ transientInstance.getCustId() + "',prd_inst_id='"
						+ transientInstance.getPrdInstId() + "',accs_nbr='"
						+ transientInstance.getAccsNbr() + "',all_accs_nbr='"
						+ transientInstance.getAllAccsNbr() + "',camp_id='"
						+ transientInstance.getCampId()
						+ "',camp_import_seq_id='"
						+ transientInstance.getCampImportSeqId()
						+ "',clu_type='" + transientInstance.getCluType()
						+ "',camp_seq_id='" + transientInstance.getCampSeqId()
						+ "',user_id='" + transientInstance.getUserId()
						+ "',is_appointed="
						+ transientInstance.getIsAppointed() + " where seq_id="
						+ transientInstance.getSeqId();
			else
				sql = "insert into " + db
						+ ".Camp_Cust_Appd_Rslt_Temp values('"
						+ transientInstance.getSeqId() + "','"
						+ transientInstance.getLatnId() + "','"
						+ transientInstance.getCustId() + "','"
						+ transientInstance.getPrdInstId() + "','"
						+ transientInstance.getAccsNbr() + "','"
						+ transientInstance.getAllAccsNbr() + "','"
						+ transientInstance.getCampId() + "','"
						+ transientInstance.getCampImportSeqId() + "','"
						+ transientInstance.getCluType() + "','"
						+ transientInstance.getCampSeqId() + "','"
						+ transientInstance.getUserId() + "','"
						+ transientInstance.getIsAppointed() + "')";
			rs = dao.insert(sql);
			if (rs2 != null) {

				rs2.close();
			}
		} catch (Exception re) {
			System.out.println("save failed:" + re);
		}
		finally
		{
			dao.closeconn();
		}
		return rs;

	}
	
	public int insertfromsql(String mysql,int campid,String clu_type,String latnid) {
		Constant cont = new Constant();
		String latncd=(new SysLocLatnDAO()).findById("latn_id", latnid).getLatn_Cd();
		String bas_prd_table=cont.getLatnPara("BAS_PRD_INST_TABLE",latncd);
		String prd_in_cnd=cont.getPara("PRD_IN_COND");
		String bas_cust_table=cont.getLatnPara("BAS_CUST_TABLE_CUR",latncd);
		int rs = 0;
		try {
			String sql = null;
			//System.out.println("hh"+clu_type);
			if(clu_type.indexOf("1")>-1) //prd_inst_id
			      sql =	"insert into " + db
						+ ".Camp_Cust_Appd_Rslt_Temp(latn_id,cust_id,prd_inst_id,Accs_nbr,All_Accs_Nbr,Camp_Id,Clu_Type,camp_seq_id,user_id) "+
								"select '"+latnid+"',"+cont.getPara("CUST_ID_CAST")+","+cont.getPara("PRD_INST_ID_CAST")+",Accs_nbr,Accs_nbr,"
						        + campid+",'"+clu_type+"',0,0 from "+bas_prd_table
						        +" where "+prd_in_cnd + " and prd_inst_id in ("+mysql+") "
						        +" and prd_inst_id not in (select prd_inst_id from " + db + ".Camp_Cust_Appd_Rslt_Temp "
						        +" where Camp_Id = " + campid + " and (Camp_Seq_Id=0 or Camp_Seq_Id is null))";
			else //cust_id
				sql =	"insert into " + db
				+ ".Camp_Cust_Appd_Rslt_Temp(latn_id,cust_id,prd_inst_id,Accs_nbr,All_Accs_Nbr,Camp_Id,Clu_Type) "+
				"select '"+latnid+"',"+cont.getPara("CUST_ID_CAST")+",'',Rec_Contact_Accs_Nbr,Rec_Contact_Accs_Nbr||','||Contact_Telephone,"
				+ campid+",'"+clu_type+"' from "+bas_cust_table
				+" where (chars(Rec_Contact_Accs_Nbr)>1 or  chars(Contact_Telephone)>1) "
				+" and cust_id in ("+mysql+") "
				+" and cust_id not in (select cust_id from " + db + ".Camp_Cust_Appd_Rslt_Temp "
		        +" where Camp_Id = " + campid + "  and (Camp_Seq_Id=0 or Camp_Seq_Id is null))"
		        + cont.getPara("IMPORT_CUST_QUALIFY");
			sql=(new Constant()).changeLatnId(latncd, sql);
			rs = dao.insert(sql);

		} catch (Exception re) {
			System.out.println("save failed:" + re);
		}
		return rs;
	}
	
	public int insertfromclu(int custcluid,int campid,String clu_type,String latnid) {
		int rs = 0;
		try {
			String sql = "select prd_inst_id from " + db + ".cust_clu_res where latn_id='"+latnid+"' and cust_clu_id="+custcluid;
            rs=insertfromsql(sql,campid,clu_type,latnid);
		} catch (Exception re) {
			System.out.println("save failed:" + re);
		}
		return rs;
	}
	
	public int insertonerow(String campId,String nbrType,String accsNbr,String latncd,String latnid) {
		int rs = 0;
		Constant cont = new Constant();
		//String latncd=(new SysLocLatnDAO()).findById("latn_id", latnid).getLatn_Cd();
		String bas_prd_table=cont.getLatnPara("BAS_PRD_INST_TABLE",latncd);
		String bas_cust_table=cont.getLatnPara("BAS_CUST_TABLE_CUR",latncd);
		String prd_in_cnd=cont.getPara("PRD_IN_COND");
		String cust_in_cnd=cont.getPara("CUST_IN_COND");
		try {
			String sql =null;
			if(!nbrType.equals("cust_id"))
				sql ="insert into " + db
				+ ".Camp_Cust_Appd_Rslt_Temp(latn_id,cust_id,prd_inst_id,Accs_nbr,All_Accs_Nbr,Camp_Id,Clu_Type,camp_seq_id,user_id) "+
				"select '"+latnid+"',"+cont.getPara("CUST_ID_CAST")+","+cont.getPara("PRD_INST_ID_CAST")+",'','',"
				+ campId+",'1',0,0 from "+bas_prd_table
				+" where "+prd_in_cnd + " and "+nbrType+"='"+accsNbr+"'"
				+" and " + nbrType + " not in (select " + nbrType + " from " + db + ".Camp_Cust_Appd_Rslt_Temp "
				+" where Camp_Id = " + campId + " and (Camp_Seq_Id=0 or Camp_Seq_Id is null)) "+cont.getPara("IMPORT_PRD_QUALIFY");
		    else
		    	sql ="insert into " + db
				+ ".Camp_Cust_Appd_Rslt_Temp(latn_id,cust_id,prd_inst_id,Accs_nbr,All_Accs_Nbr,Camp_Id,Clu_Type,camp_seq_id,user_id) "+
				"select '"+latnid+"',"+cont.getPara("CUST_ID_CAST")+",'',Rec_Contact_Accs_Nbr,Rec_Contact_Accs_Nbr,"
		        + campId+",'2',0,0 from "+bas_cust_table
		        +" where "+cust_in_cnd + " and "+nbrType+"='"+accsNbr+"'"
		        +" and " + nbrType + " not in (select " + nbrType + " from " + db + ".Camp_Cust_Appd_Rslt_Temp "
		        +" where Camp_Id = " + campId + " and (Camp_Seq_Id=0 or Camp_Seq_Id is null)) "+cont.getPara("IMPORT_CUST_QUALIFY");
		    	sql=(new Constant()).changeLatnId(latncd, sql);
		    	System.out.println(sql);
		    	rs = dao.insert(sql);
		} catch (Exception re) {
			System.out.println("save failed:" + re);
		}
		return rs;
	}

	public void delete(CampCustAppdRsltTemp persistentInstance) {

		String sql = "delete from " + db
				+ ".Camp_Cust_Appd_Rslt_Temp where Seq_Id = "
				+ persistentInstance.getSeqId();

		try {
			dao.delete(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public CampCustAppdRsltTemp findById(String id) {

		String sql = "select * from " + db
				+ ".Camp_Cust_Appd_Rslt_Temp where Seq_Id = " + id;
		CampCustAppdRsltTemp sd = new CampCustAppdRsltTemp();
		try {
			ResultSet rs = dao.select(sql);
			if (rs.next()) {
				sd.setSeqId(rs.getInt(1));
				sd.setLatnId(rs.getString(2));
				sd.setCustId(rs.getString(3));
				sd.setPrdInstId(rs.getString(4));
				sd.setAccsNbr(rs.getString(5));
				sd.setAllAccsNbr(rs.getString(6));
				sd.setCampId(rs.getInt(7));
				sd.setCampImportSeqId(rs.getInt(8));
				sd.setCluType(rs.getString(9));
				sd.setCampSeqId(rs.getInt(10));
				sd.setUserId(rs.getInt(11));
				sd.setIsAppointed(rs.getString(12));
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return sd;

	}

	public List findAll() {

		String sql = "select * from " + db + ".Camp_Cust_Appd_Rslt_Temp";
		List list = new ArrayList();
		try {
			ResultSet rs = dao.select(sql);
			while (rs.next()) {
				CampCustAppdRsltTemp sd = new CampCustAppdRsltTemp();
				sd.setSeqId(rs.getInt(1));
				sd.setLatnId(rs.getString(2));
				sd.setCustId(rs.getString(3));
				sd.setPrdInstId(rs.getString(4));
				sd.setAccsNbr(rs.getString(5));
				sd.setAllAccsNbr(rs.getString(6));
				sd.setCampId(rs.getInt(7));
				sd.setCampImportSeqId(rs.getInt(8));
				sd.setCluType(rs.getString(9));
				sd.setCampSeqId(rs.getInt(10));
				sd.setUserId(rs.getInt(11));
				sd.setIsAppointed(rs.getString(12));
				list.add(sd);
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return list;

	}
	
	public int count(String campid) {

		String sql = "select count(*) as total from " + db + ".Camp_Cust_Appd_Rslt_Temp where camp_id="+campid;
		int cnt=0;
		try {
			ResultSet rs = dao.select(sql);
			if(rs.next()) {
				cnt=rs.getInt(1);
			}
			if (rs != null) {

				rs.close();
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return cnt;

	}
	
	public int count(String campid,String campseqid) {

		String sql = "select count(*) as total from " + db 
		        + ".Camp_Cust_Appd_Rslt_Temp where camp_id="+campid+" and camp_seq_id="+campseqid;
		int cnt=0;
		try {
			ResultSet rs = dao.select(sql);
			if(rs.next()) {
				cnt=rs.getInt(1);
			}
			if (rs != null) {

				rs.close();
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return cnt;

	}
	
	public int countuse(String campid) {

		String sql = "select count(*) as total from " + db 
		        + ".Camp_Cust_Appd_Rslt_Temp where camp_id="+campid+" and camp_seq_id>0";
		int cnt=0;
		try {
			ResultSet rs = dao.select(sql);
			if(rs.next()) {
				cnt=rs.getInt(1);
			}
			if (rs != null) {

				rs.close();
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return cnt;

	}
	
	public int updateflag(String campid) {

		String sql = "select count(*) as total from " + db 
		        + ".Camp_Cust_Appd_Rslt_Temp where camp_id="+campid+" and camp_seq_id>0";
		int cnt=0;
		try {
			ResultSet rs = dao.select(sql);
			if(rs.next()) {
				cnt=rs.getInt(1);
			}
			if (rs != null) {

				rs.close();
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return cnt;

	}

}
